from jobs.sql_mehods import *

db = MySQLHelper(host='mmservice-05.mysql.hotgrid.cn', port=3306,
                 user='electricity_api_service', password='GJlfh7&#jg',
                 db='electricity_data', charset='utf8')

sql = f"""
 select t1.data_time, t1.ent_id, t1.yield_dev_id, t1.dev_name as yield_dev_name, t1.status as status1, t1.value as yield_value, t1.rated_power as yield_rated_power,
               t2.treat_dev_id as dev_id, t2.dev_name, t2.status as status2, t2.value as treat_value, t2.rated_power as treat_rated_power,
               t1.project_id, t2.ent_name, t2.industry_type_id,
               t2.industry_type, t2.county_id, t2.county_name, t2.town_id, t2.town_name,
                4 as warn_type from
                    (select distinct a.id as ent_id,
                            c.yield_dev_id,
                           c.treat_dev_id,
                           b.dev_name,
                            d.status,
                            d.data_time,
                            a.project_id,
                                     ifnull(b.median_rated_power, b.rated_power) as rated_power,
                                     f.value
                    from elec_enterprise_info as a
                        left join rel_yield_treat as c on c.ent_id=a.id
                        left join elec_dev_info as b on b.ent_id = c.ent_id and b.id=c.yield_dev_id
                        left join (select guid, devid,project_id from elecdata_basic_info where name='总正向有功电度' and is_deleted=0) as e on b.id = e.devid
                        left join elecdata_dev_status as d on b.guid=d.guid
                        left join elecdata_convert_data as f on f.guid = e.guid and f.data_time=d.data_time and f.time_type=1
                        where d.data_time>'2024-01-23 12:00:00' and a.is_deleted=0 and b.is_deleted=0 and c.is_deleted=0 and d.is_deleted=0 and f.is_deleted=0
                        and b.dev_name not like '%数码印刷机%'
                        ) as t1
                inner join
                    (select distinct a.id as ent_id,
                    a.ent_name, a.industry_type_id, e.industry_type, a.county_id, a.county_name, a.town_id, a.town_name,
                           c.treat_dev_id,
                           b.dev_name,
                            d.status,
                            d.data_time,
                            a.project_id,
                                     ifnull(b.median_rated_power, b.rated_power) as rated_power,
                            g.value
                    from elec_enterprise_info as a
                    left join dict_industry_type as e on a.industry_type_id=e.id
                        left join rel_yield_treat as c on c.ent_id=a.id
                        left join elec_dev_info as b on b.ent_id = c.ent_id and b.id=c.treat_dev_id
                        left join (select guid, devid,project_id from elecdata_basic_info where name='总正向有功电度' and is_deleted=0) as f on b.id = f.devid
                        left join elecdata_dev_status as d on b.guid=d.guid
                        left join elecdata_convert_data as g on g.guid = f.guid and g.data_time=d.data_time and g.time_type=1
                        where d.data_time>'2024-01-23 12:00:00' and a.is_deleted=0 and b.is_deleted=0 and c.is_deleted=0 and d.is_deleted=0 and g.is_deleted=0
                        ) as t2
                on t1.treat_dev_id=t2.treat_dev_id and t1.ent_id=t2.ent_id and t1.data_time=t2.data_time
                where t1.value>=0.2 and t1.value>=(t1.rated_power*0.2) and t2.value<(t2.rated_power*0.1)
"""


data=db.execute_select(sql)
import pandas as  pd
data= pd.DataFrame(data)

data.to_csv(r"测试数据集.csv")